Date Functions

About the Methods Used to Calculate Time Periods

There are two ways to calculate when a time period has passed: the boundary crossing method and the whole interval method.

With the boundary crossing method, the application considers a time period has passed whenever you cross over the boundary. For example, if you are calculating days, the boundary is midnight. If you start a process at 11:58 PM and end at 12:02 AM the next day, using the boundary crossing method, the application considers that a day has passed, even though the process took only four minutes.

With the whole interval method, the application considers a time period has passed whenever a whole interval has passed. For example, if you are calculating days, the whole interval is one full day, or 24 hours. If you start a process at 11:58 PM today, the application only considers a day to pass after 11:58 PM tomorrow. That is, after 24 hours have passed.

When calculating months, if you are going from a longer month (such as August, which has 31 days) to a shorter month (such as September which has 30 days), and you start the interval on a day of the month in the longer month that does not exist in the shorter month (for example, if you start the interval on August 31), the application considers the interval to have passed on the last day of the next month (for example, on September 30).

Microsoft SQL Server rules use the boundary crossing method.

About Time Zones

Many date functions have a timeZone parameter. This parameter specifies time zones as they appear in the IANA time zone database format. See Working with Dates, Times, and Time Zones for more information.

Rarely, the result of certain functions can vary depending on your time zone. For the DiffDays(), DiffHours(), DiffMinutes(), DiffMonths(), DiffQuarters(), DiffSeconds(), DiffWeeks(), and DiffYears() functions, the functions calculate the number of whole intervals in the current time zone, even though it returns UTC. Normally, a day is 24 hours, but occasionally there are 23 or 25 hour days when daylight savings time begins or ends. So on those days, you would need to pass 23 or 25 hours to pass a whole interval.